﻿Public Class OPERATITEM
    Private OPERATID As Integer = Nothing
    Private PRDCODE As Integer = Nothing
    Private PRDCAT As Integer = Nothing
    Private CODE As String = Nothing
    Private ICD10TM As String = Nothing
    Private ICD9CM As String = Nothing
    Private FCODE As String = Nothing
    Private OPERATENAME_EN As String = Nothing
    Private OPERATENAME_TH As String = Nothing
    Private CHKIN = Nothing
    Private REF As String = Nothing
    Private STTDATE As String = Nothing
    Private EXPDATE As String = Nothing
    Private COST As String = Nothing
    Private STATUS = Nothing
    Private CCODE As Integer = Nothing
    Private RCODE As Integer = Nothing
    Private OPDPRC As Double = Nothing
    Private IPDPRC As Double = Nothing
    Private F_STK = Nothing
    Private F_FC = Nothing
    Private F_DF = Nothing
    Private GRPID As Integer = Nothing
    Private SelectOperatitem = "SELECT `operatid` AS 'รหัสหัตถการ', mpd.`prdcode` AS 'PRDCODE', pd.`catname` AS 'ประเภท', oi.`code` AS 'CODE', `icd10tm` AS 'ICD10TM', `icd9cm` AS 'ICD9CM', `fcode` AS 'FCODE', `operatename_en` AS 'ชื่อหัตถการภาษาอังกฤษ', `operatename_th`AS 'ชื่อหัตถการภาษาไทย', `chkin` AS 'CHKIN', `ref` AS 'หมายเหตุ', `sttdate` AS 'วันที่เริ่มใช้', `expdate` AS 'วันที่เลิกใช้', `cost` AS 'ต้นทุน', oi.`status` AS 'สถานะการเปิดใช้งาน', mpd.`ccode` AS 'CCODE', finchcode.`name` AS 'รหัสรายได้', mpd.`rcode` AS 'RCODE', finrecode.`name` AS 'รหัสรายรับทางการเงิน', mpd.`ipdprc` AS 'ราคาขาย IPD', mpd.`opdprc`AS 'ราคาขาย OPD', mpd.`f_stk` AS 'ตัดสต๊อค', masgrpoper.`grpid`, masgrpoper.`code` AS 'code กลุ่มหัตถการ', masgrpoper.`grpname` AS 'ชื่อกลุ่มหัตถการ', mpd.`f_fc` AS 'เพิ่มในการเงิน', oi.`f_df` AS 'Doctor Fee' FROM operatitem oi LEFT JOIN masproduct AS mpd ON oi.prdcode = mpd.prdcode LEFT JOIN masprdcats AS pd ON oi.prdcat = pd.prdcat LEFT JOIN (SELECT `grpid`,`code`,`grpname` FROM masgrpoper WHERE `status` = 1) AS masgrpoper ON oi.`grpid` = masgrpoper.`grpid` LEFT JOIN (SELECT `ccode`,`name` FROM finchcode WHERE `status` = 1) AS finchcode ON mpd.`ccode` = finchcode.`ccode` LEFT JOIN (SELECT `rcode`,`name` FROM finrecode WHERE `status` = 1) AS finrecode ON mpd.`rcode` = finrecode.`rcode`;"
    Dim connect As CONDB = CONDB.NewConnection()
    'Dim connectlocal As conlocal = conlocal.NewConnection
    Property GRPID_() As Integer
        Get
            Return GRPID
        End Get
        Set(value As Integer)
            GRPID = value
        End Set
    End Property
    Property IPDPRC_() As Double
        Get
            Return IPDPRC
        End Get
        Set(value As Double)
            IPDPRC = value
        End Set
    End Property

    Property OPDPRC_() As Double
        Get
            Return OPDPRC
        End Get
        Set(value As Double)
            OPDPRC = value
        End Set
    End Property

    Property RCODE_() As Integer
        Get
            Return RCODE
        End Get
        Set(value As Integer)
            RCODE = value
        End Set
    End Property

    Property CCODE_() As Integer
        Get
            Return CCODE
        End Get
        Set(value As Integer)
            CCODE = value
        End Set
    End Property

    Property COST_() As String
        Get
            Return COST
        End Get
        Set(value As String)
            COST = value
        End Set
    End Property

    Property STTDATE_() As String
        Get
            Return STTDATE
        End Get
        Set(value As String)
            STTDATE = value
        End Set
    End Property

    Property EXPDATE_() As String
        Get
            Return EXPDATE
        End Get
        Set(value As String)
            EXPDATE = value
        End Set
    End Property

    Property REF_() As String
        Get
            Return REF
        End Get
        Set(value As String)
            REF = value
        End Set
    End Property

    Property CHKIN_() As Boolean
        Get
            Return CHKIN
        End Get
        Set(value As Boolean)
            If value = True Then
                CHKIN = 1
            Else
                CHKIN = 0
            End If
        End Set
    End Property
    Property F_STK_() As Boolean
        Get
            Return F_STK
        End Get
        Set(value As Boolean)
            If value = True Then
                F_STK = 1
            Else
                F_STK = 0
            End If
        End Set
    End Property

    Property F_FC_() As Boolean
        Get
            Return F_FC
        End Get
        Set(value As Boolean)
            If value = True Then
                F_FC = 1
            Else
                F_FC = 0
            End If
        End Set
    End Property

    Property F_DF_() As Boolean
        Get
            Return F_DF
        End Get
        Set(value As Boolean)
            If value = True Then
                F_DF = 1
            Else
                F_DF = 0
            End If
        End Set
    End Property

    Property OPERATENAME_TH_() As String
        Get
            Return OPERATENAME_TH
        End Get
        Set(value As String)
            OPERATENAME_TH = value
        End Set
    End Property

    Property OPERATENAME_EN_() As String
        Get
            Return OPERATENAME_EN
        End Get
        Set(value As String)
            OPERATENAME_EN = value
        End Set
    End Property

    Property FCODE_() As String
        Get
            Return FCODE
        End Get
        Set(value As String)
            FCODE = value
        End Set
    End Property

    Property ICD9CM_() As String
        Get
            Return ICD9CM
        End Get
        Set(value As String)
            ICD9CM = value
        End Set
    End Property

    Property ICD10TM_() As String
        Get
            Return ICD10TM
        End Get
        Set(value As String)
            ICD10TM = value
        End Set
    End Property

    Property CODE_() As String
        Get
            Return CODE
        End Get
        Set(value As String)
            CODE = value
        End Set
    End Property

    Property PRDCAT_() As Integer
        Get
            Return PRDCAT
        End Get
        Set(value As Integer)
            PRDCAT = value
        End Set
    End Property

    Property PRDCODE_() As Integer
        Get
            Return PRDCODE
        End Get
        Set(value As Integer)
            PRDCODE = value
        End Set
    End Property

    Property OPERATID_() As Integer
        Get
            Return OPERATID
        End Get
        Set(value As Integer)
            OPERATID = value
        End Set
    End Property

    Property STATUS_() As Boolean
        Get
            Return STATUS
        End Get
        Set(value As Boolean)
            If value = True Then
                STATUS = 1
            Else
                STATUS = 0
            End If
        End Set
    End Property

    Property SelectOperatitem_() As String
        Get
            Return SelectOperatitem
        End Get
        Set(value As String)

        End Set
    End Property
    Public Sub loadcombobox3(ByVal CCODECmb As ComboBox)
        Dim FINCHCODE As DataTable
        FINCHCODE = connect.GetTable("SELECT `ccode`, `name` FROM finchcode WHERE `status` = 1 ")
        Try
            With CCODECmb
                .DataSource = FINCHCODE
                .DisplayMember = "name"
                .ValueMember = "ccode"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Public Sub loadcombobox4(ByVal RCODECmb As ComboBox)
        Dim FINRECODE As DataTable
        FINRECODE = connect.GetTable("SELECT `rcode`, `name` FROM finrecode WHERE `status` = 1 ")
        Try
            With RCODECmb
                .DataSource = FINRECODE
                .DisplayMember = "name"
                .ValueMember = "rcode"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Public Function insertOperatitemData() As Boolean
        If TypeOf OPERATENAME_TH Is String And TypeOf OPERATENAME_EN Is String Then
            If connect.GetTable("SELECT `operatid` FROM operatitem WHERE  `code` = '" & CODE & "' AND `operatename_en` = '" & OPERATENAME_EN & "' AND `operatename_th` = '" & OPERATENAME_TH & "'").Rows.Count > 0 Then
                MsgBox("มีข้อมูลส่วนนี้อยู่ในระบบแล้ว", MsgBoxStyle.Critical)
                Return False
            Else
                connect = CONDB.NewConnection()
                connect.BeginTrans()
                Try
                    Dim productCode As Integer
                    productCode = connect.ExecuteScalar("INSERT INTO masproduct (`prdcat`, `prdname`, `ccode`, `rcode`,`opdprc`,`ipdprc`,`f_stk`,`f_fc`) VALUES ('3', '" & OPERATENAME_EN & "', '" & CCODE & "', '" & RCODE & "','" & OPDPRC & "','" & IPDPRC & "','" & F_STK & "', '" & F_FC & "'); SELECT LAST_INSERT_ID();")
                    Dim sql As String
                    sql = "INSERT INTO operatitem"
                    sql += "(`code`,`prdcode`,`icd10tm`,`icd9cm`,`fcode`,`operatename_en`,`operatename_th`,`ref`,`sttdate`,`expdate`,`cost`,`chkin`,`grpid`,`f_df`,`status`)"
                    sql += "VALUES ("
                    sql += "'" & CODE & "',"
                    sql += "'" & productCode & "',"
                    sql += "'" & ICD10TM & "',"
                    sql += "'" & ICD9CM & "',"
                    sql += "'" & FCODE & "',"
                    sql += "'" & OPERATENAME_EN & "',"
                    sql += "'" & OPERATENAME_TH & "',"
                    sql += "'" & REF & "',"
                    sql += "'" & STTDATE & "',"
                    sql += "'" & EXPDATE & "',"
                    sql += "'" & COST & "',"
                    sql += "'" & CHKIN & "',"
                    sql += "'" & GRPID & "',"
                    sql += "'" & F_DF & "',"
                    sql += "'" & STATUS & "')"
                    connect.ExecuteNonQuery(sql)
                    connect.CommitTrans()
                    MsgBox("ท่านได้ทำการเพิ่มข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
                    Return True
                Catch ex As Exception
                    connect.RollbackTrans()
                    MsgBox(ex.ToString)
                    Return False
                Finally
                    connect.Dispose()
                End Try
            End If
        Else
            MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร", MsgBoxStyle.Critical)
            Return False
        End If
    End Function

    Public Function editOperatitemData() As Boolean
        If TypeOf OPERATENAME_TH Is String And TypeOf OPERATENAME_EN Is String Then
            connect = CONDB.NewConnection()
            connect.BeginTrans()
            Try
                Dim sql As String
                sql = "UPDATE operatitem SET"
                sql += "`code` = '" & CODE & "',"
                sql += "`icd10tm` = '" & ICD10TM & "',"
                sql += "`icd9cm` = '" & ICD9CM & "',"
                sql += "`fcode` = '" & FCODE & "',"
                sql += "`operatename_en` = '" & OPERATENAME_EN & "',"
                sql += "`operatename_th` = '" & OPERATENAME_TH & "',"
                sql += "`ref` = '" & REF & "',"
                sql += "`sttdate` = '" & STTDATE & "',"
                sql += "`expdate` = '" & EXPDATE & "',"
                sql += "`cost` = '" & COST & "',"
                sql += "`chkin` = '" & CHKIN & "',"
                sql += "`grpid` = '" & GRPID & "',"
                sql += "`f_df` = '" & F_DF & "', "
                sql += "`status` = '" & STATUS & "' "
                sql += "WHERE `operatid` = '" & OPERATID & "';"

                sql += "UPDATE masproduct SET "
                sql += "`prdname` = '" & OPERATENAME_EN & "', "
                sql += "`ccode` = '" & CCODE & "', "
                sql += "`rcode` = '" & RCODE & "', "
                sql += "`opdprc` = '" & OPDPRC & "', "
                sql += "`f_stk` = '" & F_STK & "', "
                sql += "`f_fc` = '" & F_FC & "', "
                sql += "`ipdprc` = '" & IPDPRC & "' "
                sql += "WHERE `prdcode` = '" & PRDCODE & "';"

                connect.ExecuteNonQuery(sql)
                connect.CommitTrans()
                MsgBox("ท่านได้ทำการแก้ไขข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
                Return True
            Catch ex As Exception
                connect.RollbackTrans()
                MsgBox(ex.ToString)
                Return False
            Finally
                connect.Dispose()
            End Try
        Else
            MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร", MsgBoxStyle.Critical)
            Return False
        End If
    End Function
End Class
